using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VSTUELI
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "TST":
                case "SXN":
                case "UBT":
                default:
                    #region=========
                    dwReturn["STUELIMANDANT"] = "00";
                    dwReturn["STUELIPKEN"] = "3";
                    dwReturn["STUELISAPD"] = "00000000      Q5-TEST                 ";
                    dwReturn["STUELIPSA"] = "30";
                    dwReturn["STUELIPPOS"] = "01  ";
                    dwReturn["STUELISKEN"] = "0";
                    dwReturn["STUELISASB"] = "P                                     ";
                    dwReturn["STUELISSA"] = "33";
                    dwReturn["STUELIDATEN"] = "0520                                            000000R                                                         ";
                    #endregion
                    break;
                case "JLT":
                    break;
            }
        }

        public void InsertSTUELI(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            switch (app.company)
            {
                case "YGR":
                case "TST":
                case "SXN":
                case "UBT":
                default:
                    or1.CommandText = "INSERT INTO STUELI ( STUELIMANDANT,STUELIPKEN,STUELISAPD,STUELIPSA,STUELIPPOS,STUELISKEN,STUELISASB,STUELISSA,STUELIDATEN)"
                   + " VALUES (:STUELIMANDANT:,:STUELIPKEN:,:STUELISAPD:,:STUELIPSA:,:STUELIPPOS:,:STUELISKEN:,:STUELISASB:,:STUELISSA:,:STUELIDATEN:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QSTU01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT SUBSTR(STUELIDATEN,1,4) AS RATIO, M.* FROM STUELI M  "
              + " WHERE STUELIMANDANT=:STUELIMANDANT: AND STUELIPKEN=:STUELIPKEN: AND STUELISAPD=:STUELISAPD:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QSTU02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT S.* FROM STUELI S WHERE"
            + " STUELIMANDANT LIKE'" + dwData["STUELIMANDANT"].ToString().Trim() + "%' AND SUBSTR(STUELISAPD,15) LIKE'" + dwData["STUELISAPD"].ToString().Trim() + "%'";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QSTU03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT S.* FROM STUELI S WHERE"
            + " STUELIMANDANT LIKE'" + dwData["STUELIMANDANT"].ToString().Trim() + "%' AND SUBSTR(STUELISAPD,15,20) LIKE'" + (dwData["STUELISAPD"].ToString().Trim()).PadRight(20) + "'";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public void DSTU00(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM STUELI "
                                + " WHERE STUELIMANDANT=:STUELIMANDANT: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DSTU01(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM STUELI "
                                + " WHERE STUELIMANDANT=:STUELIMANDANT: AND STUELIPKEN=:STUELIPKEN: AND STUELISAPD=:STUELISAPD:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }

        public void DSTU02(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM STUELI "
                                + " WHERE STUELIMANDANT=:STUELIMANDANT: AND STUELIPKEN=:STUELIPKEN: AND STUELISAPD=:STUELISAPD: AND STUELIPPOS=:STUELIPPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }

        public void UpdateSTUELI(DataRow dwData, int option = 1)
        {

            if (option == 0)
            {
                switch (app.company)
                {
                    case "YGR":
                    case "TST":
                    case "SXN":
                    case "UBT":
                    default:
                        USTU01(dwData);
                        break;

                }
            }
        }
        public void USTU01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE STUELI SET STUELIMANDANT=:STUELIMANDANT:,STUELIPKEN=:STUELIPKEN:,STUELISAPD=:STUELISAPD:,STUELIPSA=:STUELIPSA:,STUELIPPOS=:STUELIPPOS:,STUELISKEN=:STUELISKEN:,STUELISASB=:STUELISASB:,STUELISSA=:STUELISSA:,STUELIDATEN=:STUELIDATEN:"
                + " WHERE STUELIMANDANT=:STUELIMANDANT: AND STUELIPKEN=:STUELIPKEN: AND STUELISAPD=:STUELISAPD: AND STUELIPPOS=:STUELIPPOS:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
    }
}
